import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import au.com.bytecode.opencsv.CSVReader;


public class Database {
	
	
	private String password = "823158";
	private String userName = "50902919";
	private String serverName = "127.0.0.1";
	private String portNumber = "3306";
	private String fileName;
	private String DbName = "Bus_Routing";
	private String dbms = "mysql";
	private String tableName = "dataTable";
	private String[] colName;
	private int numOfRow;
	private int numofCol;
    private int count = 0;
    private Connection connection;
    private String exQuery = "";
	
	
	public Database () {
		
	}
	
	public Database (String fileName) {
		this.fileName = fileName;
		parseFromFile();
	}
	
	/*
	 * To do: Return connection
	 */
	
	private Connection getConnection() {
		Connection conn = null;
		try {
			Properties connectionProps = new Properties();
		    connectionProps.put("user", this.userName);
		    connectionProps.put("password", this.password);

		    if (this.dbms.equals("mysql")) {
		        conn = DriverManager.getConnection(
		                   "jdbc:" + this.dbms + "://" +
		                   this.serverName +
		                   ":" + this.portNumber + "/",
		                   connectionProps);
		    }
		    
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println("Connected to database!");
		return conn;
	}
	
	private void closeConnection(Connection conn) {
		try {
			conn.close();
			System.out.println("Close connection to database!");
			System.out.println("");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	
	/*
	 * To do: create Database
	 */
	private void createDatabase() {
		try {
			 Connection connection = getConnection();
		     Statement statement = connection.createStatement();
		     String query1 = "create database if not exists " + DbName;
		     statement.executeUpdate(query1);
			 
             String query2 = "use " + DbName;
             statement.executeUpdate(query2);
			 
             String query3 = "drop table if exists " + tableName;
             statement.executeUpdate(query3);


             String query4 = createTableQuery();
             statement.executeUpdate(query4);
             System.out.println("Create Database successful!");
			 
			 closeConnection(connection);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	private String createTableQuery() {
		String query = "create table if not exists " + tableName + "(";
		for(int i = 0; i < numofCol; i++) {
			if(i != 0) query += ",";
			if(i == numofCol - 1) query += "col" + i + "  TIMESTAMP";
			else query += "col" + i + "  DECIMAL(20, 10)";
		}
		query += ")";
		return query;
	}
	
	/*
	 * To do: read file and insert data to Database
	 */
	
	private void parseFromFile () {
		try {
			CSVReader reader = new CSVReader(new FileReader(fileName));
			colName = reader.readNext();
			numofCol = colName.length;
			createDatabase();
			
			String[] nextLine;
			connection = getConnection();
			while ((nextLine = reader.readNext()) != null) {
				count++;
				writeData(nextLine);
			}
			
			
			reader.close();
			closeConnection(connection);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	private void writeData(String[] data) {
		String insertDataQuery = createInsertDataQuery(data);		
		try {
			
			Statement statement = connection.createStatement();
			String query = "use " + DbName;
			statement.execute(query);
			
			statement.execute(insertDataQuery);
			System.out.println("Write data successful!\n");
			
				
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private String createInsertDataQuery(String[] data) {
		String insertDataQuery = "insert into " + tableName + "(";
		for(int i = 0; i < numofCol; i++) {
			if(i != 0) insertDataQuery += ", ";
			insertDataQuery += "col" + i;
		}
		insertDataQuery += ") values";
		
		count++;
		if(count == 1) insertDataQuery += "\n(";
		else insertDataQuery += "\n(";
		for(int i = 0; i < data.length; i++) {
			if(i != 0) insertDataQuery += ", ";
			insertDataQuery += "'" + data[i] + "'";	
		}
		insertDataQuery += ");";
		System.out.println(insertDataQuery);
		
		return insertDataQuery;
	}
	
}
